﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections;
using System.IO;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using Common.Model;
using System.Reflection;
using System.Data;
using System.Data.OleDb;

namespace stock.test
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //Hashtable stockHash = globalObject.GlobalHash;
            //foreach (DictionaryEntry stock in stockHash)
            //{
            //    Common.Model.StockVirtual virtualModel=(Common.Model.StockVirtual) stock.Value;
            //    Label1.Text += "<br/>" + virtualModel.StockCode+" "+virtualModel.StockName+" "+virtualModel.UpDownCount+" "+virtualModel.UpDownMargin+" "+virtualModel.XianJia;
            //}
            //Label1.Text += "<br/>" + BLL.Tools.GetStockByCode("500009").StockName;
          //  TestMethodRun2();
            Label1.Text = "aa";
            String path = @"F:\个人资料张超\大学通讯录\2012生源地统计结果.xls";
            String path1 = @"F:\office.xls";
            DataTable dt1 = GetExcelTable(path);
            DataTable dt2 = GetExcelTable(path1);
            Hashtable ht1 = new Hashtable();
            Hashtable ht2 = new Hashtable();
            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                person people = new person();
                people.code = dt1.Rows[i]["学号"].ToString().Trim();
                people.name = dt1.Rows[i]["姓名"].ToString().Trim();
                ht1.Add(people.code, people);
            }
             
            for (int i = 0; i < dt2.Rows.Count; i++)
            {
                person people = new person();
                if (!String.IsNullOrEmpty(dt2.Rows[i]["学号"].ToString().Trim()))
                {
                    people.code = "0" + dt2.Rows[i]["学号"].ToString().Trim();
                    people.name = dt2.Rows[i]["姓名"].ToString().Trim();
                    ht2.Add(people.code, people);
                }
            }
            String a = path;
            foreach (string key in ht1.Keys)
            {                
                if (ht2.Contains(key))
                {
                }
                else
                {
                    Label1.Text += "<br/>$" +((person) ht1[key]).name+":"+key;
                }
            }
        }
        private DataTable GetExcelTable(string uploadPath)
        {
            DataSet ds;
            string Xls_ConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + uploadPath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
            OleDbConnection Conn = new OleDbConnection(Xls_ConnStr);
            try
            {
                Conn.Open();
                string sql_str = "select * from [Sheet1$]";
                OleDbDataAdapter da = new OleDbDataAdapter(sql_str, Conn);
                ds = new DataSet();
                da.Fill(ds, "excel_data");
                Conn.Close();
            }
            catch
            {
                if (Conn.State == ConnectionState.Open)
                {
                    Conn.Close();
                }
                return null;
            }
            finally
            {
                Conn.Dispose();
            }
            if (ds == null)
            {
                return null;
            }
            if (ds.Tables.Count < 1)
            {
                return null;
            }
            return ds.Tables[0];
        }
        public void TestMethodRun2()
        {
            String str = "http://m.weather.com.cn/data/101010100.html";
            Uri uri = new Uri(str);
            System.Net.WebRequest req = System.Net.WebRequest.Create(uri);
            System.Net.WebResponse resp = req.GetResponse();
            System.IO.Stream str3 = resp.GetResponseStream();
            System.IO.StreamReader reader = new System.IO.StreamReader(str3, System.Text.Encoding.UTF8);
            String jsonString = reader.ReadToEnd().Trim();
            reader.Close();
            JavaScriptSerializer json = new JavaScriptSerializer();
            WeatherVirtual weather = json.Deserialize<WeatherVirtual>(jsonString);
        }
        private void doSome()
        {
            String path = HttpContext.Current.Server.MapPath("~/stock2.txt");
            StreamReader reader = File.OpenText(path);
            String neirong = reader.ReadToEnd();
            reader.Close();
            String[] stockTemp = neirong.Split("()".ToCharArray());
            SqlConnection conn = new SqlConnection(@"Data Source=PC-201111041418\SQLEXPRESS;Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456");
            conn.Open();
            SqlTransaction tra = conn.BeginTransaction();
            try
            {
                for (int i = 1; i <= stockTemp.Length; i++)
                {
                    if (i % 2 == 0)
                    {
                        string name = stockTemp[i - 2];
                        string code = stockTemp[i - 1];
                        string sql = "insert into Stock(Name,code,stockType) values('" + name + "','" + code + "',2)";
                        SqlCommand cmd = new SqlCommand(sql, conn);
                        cmd.Transaction = tra;
                        cmd.ExecuteNonQuery();
                    }
                }
                tra.Commit();
                Label1.Text = "ok2";
            }
            catch
            {
                tra.Rollback();
                Label1.Text = "error2";
                throw;
            }
            conn.Close();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {

        }
        public class person
        {
           public String code;
           public String name;
        }
    }
}